• 2022-08-22
  • unique

PostgreSQL Cluster (repmgr)

code repo https://dev.rievo.net/sst/pg_ha Setting up a PostgreSQL cluster with repmgr and keepalived.

Follow the these steps to configure the official PostgreSQL Repo

apt install curl ca-certificates gnupg lsb-release sudo rsync
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

apt update

Install the desired PostgreSQL version and repmgr

apt install postgresql-14 repmgr [keepalived]

Configuration

Use these templates to generate the differen configurations needed during the installation: Gitlab Repo

# create from example-data.json
repmgr/template/data.json

j2 repmgr/template/hosts.jinja2 repmgr/template/data.json
# on all hosts:
vi /etc/hosts

j2 repmgr/template/postgresql.conf.d.jinja2 repmgr/template/data.json
# on the starting primary
vi /etc/postgresql/14/main/conf.d/custom.conf
chown postgres: /etc/postgresql/14/main/conf.d/custom.conf

j2 repmgr/template/pg_hba.conf.jinja2 repmgr/template/data.json
# on the starting primary
vi /etc/postgresql/14/main/pg_hba.conf

j2 repmgr/template/repmgr.conf.jinja2 repmgr/template/data.json
# on each server the respective config
vi /etc/repmgr.conf

j2 repmgr/template/repmgrd.service.jinja2 repmgr/template/data.json
# on each server
vi /etc/systemd/system/repmgrd.service
systemctl daemon-reload

j2 repmgr/template/keepalived.conf.jinja2 repmgr/template/data.json
# on each server
vi /etc/keepalived/keepalived.conf

j2 repmgr/template/postgres_is_primary.sh.jinja2 repmgr/template/data.json
# on each server
vi /usr/local/bin/postgres_is_primary.sh
chmod +x /usr/local/bin/postgres_is_primary.sh

# on the starting primary
sudo -u postgres createuser -s repmgr
sudo -u postgres createdb repmgr -O repmgr
sudo -u postgres psql
# SET password_encryption = 'scram-sha-256';
# \password repmgr
# -- can be repeated for the postgres user

j2 repmgr/template/.pgpass.jinja2 repmgr/template/data.json
# on all servers
sudo su -  postgres
vi .pgpass
chmod 600 .pgpass

Tuning

  • https://github.com/credativ/pg_cloudconfig
  • https://pgtune.leopard.in.ua/
  • max_connections -> schwierig zu tunen
  • shared_buffers -> 25% RAM
  • effective_cache_size -> 75% RAM
  • work_mem -> schwierig zu tunen
  • maintenance_work_mem -> 1GB
  • max_wal_size -> erhoehen bis Checkpoints zeitabsiert sind
  • random_page_cost -> 1.0 fuer SSD/NVME
  • pg_stat_statements.max=10000 doesn’t use a lot of resources
  • idle_in_tansaction_session_timeout=1h
  • log_min_duration_statement=5s
  • log_lock_waits=on
  • log_temp_files=10MB
  • client verbindung verschluesseln
  • wenn moeglich ein eigenens schema, ansonsten REVOKE ALL ON SCHEMA public FROM PUBLIC, da ansonsten jeder mit login tabellen erstellen kann
  • document monitoring
  • archive command

Ops

  • SELECT * FROM pg_stat_activity;
    • kill (nicht kill -9) auf die PID des Backends
    • SELECT pg_cacel_backend('$PID') fuer Abfragen
    • SELECT pg_terminate_backend('$PID') fuer Transaktionen
  • CHECKPOINT vor herunterfahren fuehrt zu schnellerem shutdown

Update

  • roadmap, release cylce: https://www.postgresql.org/developer/roadmap/
  • minor update
    • patch installieren
    • postgresql neustarten
    • replikation: zuerst standby dann switchover dan old primary updaten
  • major update
    • mit downtime
    • in-place -> pg_upgrade hard-link modus
      • when to delete the old folder?
    • oder dump/restore langsam

backup

  • simple
    • pg_dump
    • pg_basebackup
  • dedicated
    • pgBackRest -> komisch
    • Barman

Limits

  • bis 100GB
    • daily pg_basebackup mit xlog-archivierung oder dumps
  • bis 1TB
    • dedicated backup z.B. pgBackRest
    • taegliche Backups dauern zu lange
  • ab 1TB
    • externer support

Pitfalls

  • multi master nicht wirklich machbar
  • datenverlust durch glibc sortierreihenfolge bei OS update
  • up to date bleiben
  • autovacuum muss anbleiben
  • tablespace muessen nicht verwendet werden

alternativen

  • patroni

other